package com.ycy;


import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.Collections;


@Slf4j
public class ExcelEditDemo {


    public static void main1(String[] args) throws IOException {

            /*
        1 备注红色背景
        2 按行删除，从大到小删除。注意：删除一行之后，后面的行自动提前
     */

        // 桌面路径
        String filePath = System.getProperty("user.home") + "/Desktop/居住区（居民小区）台账导入模板导入失败数据清单 (12).xlsx";

        // Load the existing Excel file
        FileInputStream fis = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0); // Modify the first sheet as an example

        // Create a cell style with red background
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // Apply the style to the fifth row (index 4)
        Row row = sheet.getRow(4);
        if (row == null) {
            row = sheet.createRow(4);
        }

        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                cell = row.createCell(i);
            }
            cell.setCellStyle(cellStyle);
        }

        Integer [] rowsToDelete = {9,10,14};

        // 将行索引数组排序，按从大到小的顺序删除
        Arrays.sort(rowsToDelete, Collections.reverseOrder());

        for (Integer rowIndexToDelete : rowsToDelete) {

            int lastRowNum1 = sheet.getLastRowNum();
            System.out.println("lastRowNum1 => " + lastRowNum1);

            if (rowIndexToDelete >= 0 && rowIndexToDelete <= lastRowNum1) {
                Row rowToDelete = sheet.getRow(rowIndexToDelete);
                if (rowToDelete != null) {
                    sheet.removeRow(rowToDelete);
                    System.out.println("delete => " + lastRowNum1);
                    // 调整剩余的行
                    int lastRowNum = sheet.getLastRowNum();
                    if (rowIndexToDelete < lastRowNum) {
                        sheet.shiftRows(rowIndexToDelete + 1, lastRowNum, -1);
                    }
                }
            }
        }


        // Write the changes back to the file
        fis.close();
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
        workbook.close();
        System.out.println("done");
    }

    public static void main(String[] args) throws IOException {
        /*
        测试 workbook 重复编辑并导出 xlsx
         */
        String filePath = System.getProperty("user.home") + "/Desktop/居住区（居民小区）台账导入模板导入失败数据清单 (12).xlsx";

        // Load the existing Excel file
        FileInputStream fis = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0); // Modify the first sheet as an example

        // Create a cell style with red background
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        try {

            // 创建单元格样式
            CellStyle unlockedCellStyle = workbook.createCellStyle();
            unlockedCellStyle.setLocked(false); // 设置单元格为未锁定

            // 获取最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            log.info("lastRowNum -> {}", lastRowNum);
            // 从第五行开始删除所有记录
            for (int i = lastRowNum; i >= 4; i--) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    sheet.removeRow(row);
                    log.info("start delete row -> {}", i);
                }
            }

            // 第五行写一条数据
            Row row = sheet.createRow(4);
            Cell cell = row.createCell(5);

            cell.setCellValue("hello world4"); // 假设所有数据都可以转换为字符串
            cell.setCellStyle(unlockedCellStyle);

            // 第6行写一条数据
            Row row5 = sheet.createRow(5);
            Cell cell5 = row5.createCell(5);

            cell5.setCellValue("hello world5"); // 假设所有数据都可以转换为字符串
            cell5.setCellStyle(unlockedCellStyle);

            log.info("start creat xlsx");

            // 将修改后的工作簿写回文件
            try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop/decodeFile1_" + System.currentTimeMillis() + ".xlsx")) {
                workbook.write(fos);
            }

            //读取 行数总数
            int lastRowNum11 = sheet.getLastRowNum();
            log.info("lastRowNum11 -> {}", lastRowNum11);

            // 获取最后一行的行号
            int lastRowNum1 = sheet.getLastRowNum();
            // 从第五行开始删除所有记录
            for (int i = lastRowNum1; i >= 4; i--) {
                Row row1 = sheet.getRow(i);
                if (row1 != null) {
                    sheet.removeRow(row1);
                }
            }

            // 第五行写一条数据
            Row row44 = sheet.createRow(4);
            Cell cell44 = row44.createCell(5);

            cell44.setCellValue("hello world44"); // 假设所有数据都可以转换为字符串
            cell44.setCellStyle(unlockedCellStyle);

            // 将修改后的工作簿写回文件
            try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop/decodeFile2_" + System.currentTimeMillis() + ".xlsx")) {
                workbook.write(fos);
            }

            log.info("successfully write ");

        } catch (IOException e) {
            e.printStackTrace();
        }


        System.out.println("done");
    }

    public static void main2(String[] args) throws IOException {
        /*
        测试 单元格复制功能
        将 xlsx 中的sheet1 复制到 sheet2 并导出 xlsx
        问题：单元格保护功能 不能复制
         */
        String filePath = System.getProperty("user.home") + "/Desktop/居住区（村庄）台账导入模板 (1) - 副本.xlsx";

        // Load the existing Excel file
        FileInputStream fis = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0); // Modify the first sheet as an example

        // Create a cell style with red background
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        try {

            // 创建单元格样式
            CellStyle unlockedCellStyle = workbook.createCellStyle();
            unlockedCellStyle.setLocked(false); // 设置单元格为未锁定

            replaceSheet(workbook.getNumberOfSheets(), workbook);
            sheet = workbook.getSheetAt(1);

            // 第五行写一条数据
            Row row = sheet.createRow(4);
            Cell cell = row.createCell(5);

            cell.setCellValue("hello world4"); // 假设所有数据都可以转换为字符串
            cell.setCellStyle(unlockedCellStyle);

            // 第6行写一条数据
            Row row5 = sheet.createRow(5);
            Cell cell5 = row5.createCell(5);

            cell5.setCellValue("hello world5"); // 假设所有数据都可以转换为字符串
            cell5.setCellStyle(unlockedCellStyle);

            log.info("start creat xlsx");

            // 将修改后的工作簿写回文件
            try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop/decodeFile1_" + System.currentTimeMillis() + ".xlsx")) {
                workbook.write(fos);
            }
            log.info("successfully write ");

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static final int DATA_ROW_IDX = 3;

    private static void replaceSheet(int numberOfSheets, Workbook workbook) {

        CellStyle unlockedCellStyle = workbook.createCellStyle();
        // 设置单元格为未锁定
        unlockedCellStyle.setLocked(true);


        for (int i = 0; i < numberOfSheets; i++) {
            Sheet oldSheet = workbook.getSheetAt(i);
            String sheetName = oldSheet.getSheetName(); // 获取旧的Sheet的名称
            Sheet newSheet = workbook.createSheet(); // 创建新的Sheet

            // 复制行
            for (int j = 0; j <= DATA_ROW_IDX; j++) {
                Row oldRow = oldSheet.getRow(j);
                if (oldRow != null) {
                    Row newRow = newSheet.createRow(j);
                    newRow.setHeight(oldRow.getHeight());
                    for (int k = oldRow.getFirstCellNum(); k < oldRow.getLastCellNum(); k++) {
                        Cell oldCell = oldRow.getCell(k);
                        if (oldCell != null) {
                            Cell newCell = newRow.createCell(k);
                            CellStyle cellStyle = oldCell.getCellStyle();
                            newCell.setCellStyle(cellStyle);
                            newCell.getCellStyle().setLocked(true);


                            CellType cellType = oldCell.getCellType();

                            switch (cellType) {
                                case STRING:
                                    newCell.setCellValue(oldCell.getStringCellValue());

//                                    newCell.setCellType(cellType);
                                    break;
                                case NUMERIC:
                                    newCell.setCellValue(oldCell.getNumericCellValue());
//                                    newCell.setCellType(cellType);
                                    break;
                                case BOOLEAN:
                                    newCell.setCellValue(oldCell.getBooleanCellValue());
//                                    newCell.setCellType(cellType);
                                    break;
                                case FORMULA:
                                    newCell.setCellFormula(oldCell.getCellFormula());
//                                    newCell.setCellType(cellType);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
            }

            // 复制合并单元格
            for (int j = 0; j < oldSheet.getNumMergedRegions(); j++) {
                CellRangeAddress cellRangeAddress = oldSheet.getMergedRegion(j);
                if (cellRangeAddress.getFirstRow() <= DATA_ROW_IDX) {
                    newSheet.addMergedRegion(cellRangeAddress);
                }
            }

//            // 启用工作表保护
//            newSheet.protectSheet("password");

//            int oldSheetIndex = workbook.getSheetIndex(oldSheet);
//            workbook.removeSheetAt(oldSheetIndex); // 删除旧的Sheet
//            workbook.setSheetName(oldSheetIndex, sheetName); // 将新的Sheet的名称设置为旧的Sheet的名称
        }
    }


}
